drop table jms_dws.dws_leave_message_receive_regist_agent_agg;
CREATE TABLE jms_dws.dws_leave_message_receive_regist_agent_agg
(
    dt                             DATE COMMENT '日期',
    leave_message_type             int comment '留言类型:1、破损 2、延误 3、遗失 4、其他',
    register_financial_center_code varchar(80) comment '登记网点代理区code',
    register_agent_code            varchar(80) comment '登记网点大区code',
    register_agent_name            varchar(80) comment '登记网点大区名称',
    register_financial_center_name varchar(80) comment '登记网点代理区名称',
    message_cnt                    bigint comment '留言总数',
    register_reply_cnt                      bigint comment '登记方已回复数',
    register_unreply_cnt                    bigint comment '登记方未回复数',
    register_end_cnt                        bigint comment '登记方已完结数',
    register_reply_rate                     double comment '登记方回复率',
    register_end_rate                       double comment '登记方完结率'
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `leave_message_type`,`register_financial_center_code`)
COMMENT '留言接受时间接受代理区汇总'
PARTITION BY RANGE(`dt`)
(
START ("2022-04-01") END ("2022-05-26") EVERY (INTERVAL 1 day)
    )
DISTRIBUTED BY HASH(`register_financial_center_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);
